
import glob
import xlrd
import json
import io
import os

# config = {
#     "hero_data.xlsx": [  #大表名
#         {"hero_desc": #字表名
#         {"field": ["id", "ch_see"], #字段
#         "replace_field": {}, #需要替换字段名
#          "extra_field": {}, #额外增加的字段数据
#           "func": "excel_to_json" #处理函数
#           }
#           }
#     ],
# }
config = {
    "hero_data.xlsx": [
        {"hero_desc": {"field": ["id", "ch_see"], "replace_field": {},
                       "extra_field": {}, "func": "excel_to_json"}}
    ],
    "item_data.xlsx": [
        {"item_base": {"field": ["template_id", "rank_id", "name", "item_name", "desc", "type", "sub_type", "pic",
                                 "req_career", "req_sex", "req_gte_level", "overlay", "use_overlay", "limit_time",
                                 "show_bag", "show_tap", "is_use", "event_id", "access_ids"],
                       "replace_field": {},
                       "extra_field": {},
                       "func": "excel_to_json"}}
    ],
    "recharge_data.xlsx": [
        {"recharge": {"field": ["class", "type", "sub_type", "rmb", "show_name", "product_id", "qqgoods_id"],
                      "func": "excel_to_json_recharge"}},
        {
            "recharge_type": {
                "field": ["class", "desc", "rmb"],
                "func": "excel_to_json",
                "replace_field": {"class":"id","desc":"name"},
                "extra_field": {},
            }
        }
    ],
    "welfare_data.xlsx": [{
    		"welfare_base": {
    			"field": ["class", "type", "rmb", "name"],
    			"replace_field": {"name": "show_name"},
    			"extra_field": {},
    			"func": "excel_to_json_recharge"
    		}
    	}],

    "task_data.xlsx": [
        {"task_base": {"field": ["task_id", "task_des"], "replace_field": {},
                       "extra_field": {}, "func": "excel_to_json"}}
    ],
    "assets_data.xlsx": [
        {"assets_base": {"field": ["id", "name","record_index"], "replace_field": {"record_index":"field"},
                         "extra_field": {}, "func": "excel_to_json"}}
    ],
    "origin_data.xlsx": [
        {"origin": {"field": ["key", "desc"], "replace_field": {},
                    "extra_field": {}, "func": "excel_to_json"}}
    ],
    #"vip_data.xlsx": [
    #    {"vip_monthlycard": {"field": ["type", "price", "title_name"],
    #                         "replace_field": {"price": "rmb", "title_name": "show_name"},
    #                         "extra_field": {"class": 4},
    #                         "func": "excel_to_json",
    #                         "fileName": "recharge_sub_type_4.json"}}
    #],
    "invest_data.xlsx":[
    {"invest_base":{"field":["type","rmb","desc","qqgoods_id","product_id"],
                    "replace_field":{"desc":"show_name"},
                    "extra_field":{"class":6},
                    "func":"excel_to_json",
                    "fileName":"recharge_sub_type_6.json" }}
    ]
}


def process_excel(excel_file, config):
    obj = __import__(__name__)
    print("开始处理%s \n" % excel_file)
    book = xlrd.open_workbook(excel_file)
    sheet_names = book.sheet_names()
    for index, sheet_name in enumerate(sheet_names):
        if sheet_name.find("#") < 0:
            continue
        for tableConfig in config:
            for tableName, configObj in tableConfig.items():
                if sheet_name.split("#")[1] != tableName:
                    continue
#                 print(configObj)
                #                 print(tableName, configObj["func"],configObj["field"])
                sheet = book.sheet_by_index(index)  # 行数
                try:
                    funcName = configObj["func"]
                except KeyError:
                    assert ValueError("config error !!!!!!!")
                if hasattr(obj, funcName):
                    func = getattr(obj, funcName)
                    func(sheet, sheet_name, configObj)
                else:
                    assert ValueError("function not found  error !!!!!!!")


# 字典排序
def sort_dict(src=None):
    new_dict = dict()
    if src is None:
        return new_dict

    for i in sorted(src):
        new_dict[i] = src[i]
    return new_dict


# excel表格转json文件
def excel_to_json(sheet, sheet_name, config):
    max_row = sheet.nrows
    # 列数
    max_column = sheet.ncols
    print(" sheet_name:%s,max_row: %d, max_column: %d" % (sheet_name, max_row, max_column))
    # 结果，数组存储
    result = []
    heads = []
    data_type = []
    use_type = []
    allLineList = {}
    hindexList = []
    headConfig = config["field"]
    try:
        replace_config = config["replace_field"]
    except KeyError:
        replace_config = dict()

    try:
        extra_config = config["extra_field"]
    except KeyError:
        extra_config = dict()
    # 解析表头
    for hindex, column in enumerate(range(max_column)):
        # 读取的话行列是从（1，1）开始
        # header 字段名
        h = sheet.cell(0, column).value

        if h not in headConfig:
            continue
        heads.append(sheet.cell(0, column).value)
        # 数据类型
        data_type.append(sheet.cell(1, column).value)
        # 区分前后端使用
        use_type.append(sheet.cell(2, column).value)
        hindexList.append(hindex)

    # i = 1
    for row in range(4, max_row):
        firstValue = 0
        s = {}
        for i, hindex in enumerate(hindexList):
            if hindex == 0:
                first_cell = sheet.cell(row, 0)
                if first_cell.value is None or first_cell.value == "":
                    continue
                firstValue = int(first_cell.value)
            desc = heads[i]
            try:
                desc = replace_config[desc]
            except KeyError:
                desc = heads[i]
            dtypes = data_type[i]
            if dtypes == "int":
                try:
                    s[desc] = int(sheet.cell(row, hindex).value)
                except ValueError:
                    s[desc] = 0
            if dtypes == 'str':
                s[desc] = str(sheet.cell(row, hindex).value)
            if dtypes == 'atom':
                v = (str(sheet.cell(row, hindex).value)).split(".")
                if len(v) >=2:
                     v =v[1]
                else:
                    v = ""

                s[desc] = v
        # print(extra_config)
        for key, value in extra_config.items():
            s[key] = value

        allLineList[str(firstValue)] = sort_dict(s)
        del s
    try:
        saveFilename = config["fileName"]
    except KeyError:
        saveFilename = "./{}.json".format(sheet_name.split("#")[1])
    # 将json保存为文件
    save_json_file(allLineList, saveFilename)


# recharge_data.xlsx
def excel_to_json_recharge(sheet, sheet_name, config):
    max_row = sheet.nrows
    # 列数
    max_column = sheet.ncols
    print(" sheet_name:%s,max_row: %d, max_column: %d" % (sheet_name, max_row, max_column))
    headConfig = config["field"]
    alldict = dict()
    # 行数
    max_row = sheet.nrows
    # 列数
    max_column = sheet.ncols
    # 结果，数组存储
    result = []
    heads = []
    data_type = []
    use_type = []
    try:
        replace_config = config["replace_field"]
    except KeyError:
        replace_config = dict()
    # 解析表头
    for column in range(max_column):
        # 读取的话行列是从（1，1）开始
        # header 字段名
        heads.append(sheet.cell(0, column).value)
        # 数据类型
        data_type.append(sheet.cell(1, column).value)
        # 区分前后端使用
        use_type.append(sheet.cell(2, column).value)
        # 遍历每一行
        # 解析表头
    for column in range(max_column):
        # 读取的话行列是从（1，1）开始
        # header 字段名
        heads.append(sheet.cell(0, column).value)
        # 数据类型
        data_type.append(sheet.cell(1, column).value)
        # 区分前后端使用
        use_type.append(sheet.cell(2, column).value)
    # 遍历每一行
    for row in range(4, max_row):
        keyindex = 0
        one_line = {}
        # 获取每一行第一列
        first_cell = sheet.cell(row, 0)
        if first_cell.value == None or first_cell.value == "":
            continue
        for column in range(max_column):
            # 读取第二行开始每一个数据
            k = heads[column]
            if k not in headConfig:
                continue
            try:
                desc = replace_config[k]
            except KeyError:
                desc = heads[column]
            k = desc
            dt = data_type[column]
            uset = use_type[column]
            if k == None:
                continue
            if uset == "server":
                continue
            # if uset  == "client" or uset == "none":
            # continue
            cell = sheet.cell(row, column)
            value = cell.value
            if k == "class":
                keyindex = int(value)
            try:
                if dt == "int":
                    if value is None or value == "":
                        value = 0
                    value = int(value)
                if dt == "float":
                    if value is None:
                        value = 0
                    value = str(value)
                if dt == "string":
                    if value is None or value == "":
                        value = ""
                    value = str(value)
                if dt == "map" or dt == "maparr":
                    if value is None or value == "":
                        value = ""
                    else:
                        value = json.loads(value)
                if dt == "term":
                    if (
                            value is None or value == ""
                    ):  # {1,1},{1,1} =[[1,1],[1,1]]   1,1
                        value = []  # [1,2,3]
                    else:
                        value = value.replace("{", "[")
                        value = value.replace("}", "]")
                        # value = value.split(",") #1,2 //{1,2}
                        # value = json.loads(value)
                        value = "[%s]" % value
                        value = json.loads(value)
                if dt in ["bool", "boolean"]:
                    if value is None:
                        value = 0
                    if value == 0:
                        value = "false"
                    else:
                        value = "true"
                if dt == "arr":
                    if value == "" or value is None:
                        value = []
                    if type(value) == type("s"):  # 1,2,3  =string  1= 1.0 =float
                        value = value.split(",")
                    if type(value) == type(0.1):
                        value = [int(value)]
                    res = list(map(int, value))
                    value = res
                one_line[k] = value
            except:
                # print("excel_file:%s, sheet_name:%s,row: %d, column: %d" % (excel_file,sheet_name,row, column))
                raise ValueError(
                    " sheet_name:%s,row: %d, column: %d value:%s"
                    % (sheet_name, row, column, value)
                )

        try:
            old_line = alldict[keyindex]
        except KeyError:
            old_line = []
        old_line.append(sort_dict(one_line))
        alldict[keyindex] = old_line
    for index, index_c in alldict.items():
#         print(index)
        fileName = "recharge_sub_type_%s.json" % index
        tmpDict = dict()
        for c in index_c:
            tmpDict[c["type"]] = c
        save_json_file(tmpDict, fileName)


# 将json保存为文件
def save_json_file(jd, json_file_name):
    fileObj = io.open(json_file_name, "w", encoding="utf-8")
    # 把对象转化为json对象
    # indent: 参数根据数据格式缩进显示，读起来更加清晰
    # ensure_ascii = True：默认输出ASCII码，如果把这个该成False, 就可以输出中文。
    fileObj.write(json.dumps(jd, indent=4, ensure_ascii=False))
    fileObj.close()


if "__main__" == __name__:
    for fileName, configObj in config.items():
        if not os.path.exists(fileName):
            print("file:%s 文件不存在跳过" %fileName)
            continue
        process_excel(fileName, configObj)
    command = "E:\phpstudy_pro\Extensions\php\php7.3.4nts\php.exe make_config.php"
    os.system(command)
#     b = os.popen(command,'r',1)
#     print(b)

